import pandas as p
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import sys

#Relative directory/file information
file = 'Tables/iv-loo.xlsx'
didFile = 'Tables/did.xlsx'
plotSaveDirec = 'Plots/LOO/'
tableSaveDirec = "Tables/LOO/"
if(not sys.platform=='darwin'):
    file = 'Tables\iv.xlsx'
    didFile = 'Tables\did.xlsx'
    plotSaveDirec = "Plots\LOO\\"
    tableSaveDirec = "Tables\LOO\\"

#Load specific LOO data
vmtLOO = p.read_excel(file, "LOO-Spec2VMT")
gasUseLOO = p.read_excel(file, "LOO-Spec2GasUse")
didLOO = p.read_excel(didFile, 'LOO-Spec2')


# gasUseLOO = gasUseLOO.rename({'P-Value(Gamma=1)':'P-Value(Gamma=1)'}, axis='columns')

#Store min and max values from travel LOO analysis
pValuesVMT16 = (min(vmtLOO['P-Value(Beta=1.6)']), max(vmtLOO['P-Value(Beta=1.6)']))
pValuesVMT1 = (min(vmtLOO['P-Value(Beta=1)']), max(vmtLOO['P-Value(Beta=1)']))
pValuesVMTNeg25 = (min(vmtLOO['P-Value(Beta=-2.5)']), max(vmtLOO['P-Value(Beta=-2.5)']))
pValuesVMT0 = (min(vmtLOO['P-Value(Beta=0)']), max(vmtLOO['P-Value(Beta=0)']))
pValuesVMT03 = (min(vmtLOO['P-Value(Beta=.3)']), max(vmtLOO['P-Value(Beta=.3)']))

#Store min and max values from gas use LOO analysis
pValuesGasUse97 = (min(gasUseLOO['P-Value(Gamma=9.7)']), max(gasUseLOO['P-Value(Gamma=9.7)']))
pValuesGasUse1 = (min(gasUseLOO['P-Value(Gamma=1)']), max(gasUseLOO['P-Value(Gamma=1)']))
pValuesGasUse11 = (min(gasUseLOO['P-Value(Gamma=11)']), max(gasUseLOO['P-Value(Gamma=11)']))
pValuesGasUseNeg48 = (min(gasUseLOO['P-Value(Gamma=-4.8)']), max(gasUseLOO['P-Value(Gamma=-4.8)']))
pValuesGasUseNeg61 = (min(gasUseLOO['P-Value(Gamma=-6.1)']), max(gasUseLOO['P-Value(Gamma=-6.1)']))



#Arrang min and max values from VMT LOO analysis
minData = np.array([[np.min(pValuesVMT16), np.min(pValuesVMT1), np.min(pValuesVMT03), np.min(pValuesVMT0), np.min(pValuesVMTNeg25)], [np.min(pValuesGasUse11), np.min(pValuesGasUse97), np.min(pValuesGasUse1), np.min(pValuesGasUseNeg48), np.min(pValuesGasUseNeg61)]])
maxData = np.array([[np.max(pValuesVMT16), np.max(pValuesVMT1), np.max(pValuesVMT03), np.max(pValuesVMT0), np.max(pValuesVMTNeg25)], [np.max(pValuesGasUse11), np.max(pValuesGasUse97), np.max(pValuesGasUse1), np.max(pValuesGasUseNeg48), np.max(pValuesGasUseNeg61)]])
rows = np.array([['Fleet Distance Model'],['Gas Use Model']])

# cols = ["", 'Null Hypothesis Value = 1.6', 'Null Hypothesis Value = 1', 'Null Hypothesis Value = -2.5', 'Null Hypothesis Value = -4']
#
#
# # minDF = p.DataFrame(np.hstack((rows, minData)), columns=cols)
# # maxDF = p.DataFrame(np.hstack((rows, maxData)), columns=cols)

vmtGasRows = np.array([['Max p-value'], ['Min p-value']])

vmtData = np.array([[np.max(pValuesVMT16), np.max(pValuesVMT1), np.max(pValuesVMT03), np.max(pValuesVMT0), np.max(pValuesVMTNeg25)], [np.min(pValuesVMT16), np.min(pValuesVMT1), np.min(pValuesVMT03), np.min(pValuesVMT0), np.min(pValuesVMTNeg25)]])
gasData = np.array([[np.max(pValuesGasUse11), np.max(pValuesGasUse97), np.max(pValuesGasUse1), np.max(pValuesGasUseNeg48), np.max(pValuesGasUseNeg61)], [np.min(pValuesGasUse11), np.min(pValuesGasUse97), np.min(pValuesGasUse1), np.min(pValuesGasUseNeg48), np.min(pValuesGasUseNeg61)]])

vmtCols = ["", 'Null Hypothesis Value = 1.6', 'Null Hypothesis Value = 1', 'Null Hypothesis Value = 0', 'Null Hypothesis Value = 0.3', 'Null Hypothesis Value = -2.5']
gasCols = ["", 'Null Hypothesis Value = 11', 'Null Hypothesis Value = 9.7', 'Null Hypothesis Value = 1', 'Null Hypothesis Value = -4.8', 'Null Hypothesis Value = -6.1']

vmtDF = p.DataFrame(np.hstack((vmtGasRows, vmtData)), columns=vmtCols)
gasDF = p.DataFrame(np.hstack((vmtGasRows, gasData)), columns=gasCols)

#Cast values to a numeric type
vmtDF.loc[:, 'Null Hypothesis Value = 1.6'] = vmtDF.loc[:, 'Null Hypothesis Value = 1.6'].astype(float)
vmtDF.loc[:, 'Null Hypothesis Value = 1'] = vmtDF.loc[:, 'Null Hypothesis Value = 1'].astype(float)
vmtDF.loc[:, 'Null Hypothesis Value = -2.5'] = vmtDF.loc[:, 'Null Hypothesis Value = -2.5'].astype(float)
vmtDF.loc[:, 'Null Hypothesis Value = 0.3'] = vmtDF.loc[:, 'Null Hypothesis Value = 0.3'].astype(float)
vmtDF.loc[:, 'Null Hypothesis Value = 0'] = vmtDF.loc[:, 'Null Hypothesis Value = 0'].astype(float)

gasDF.loc[:, 'Null Hypothesis Value = 9.7'] = gasDF.loc[:, 'Null Hypothesis Value = 9.7'].astype(float)
gasDF.loc[:, 'Null Hypothesis Value = 1'] = gasDF.loc[:, 'Null Hypothesis Value = 1'].astype(float)
gasDF.loc[:, 'Null Hypothesis Value = 11'] = gasDF.loc[:, 'Null Hypothesis Value = 11'].astype(float)
gasDF.loc[:, 'Null Hypothesis Value = -4.8'] = gasDF.loc[:, 'Null Hypothesis Value = -4.8'].astype(float)
gasDF.loc[:, 'Null Hypothesis Value = -6.1'] = gasDF.loc[:, 'Null Hypothesis Value = -6.1'].astype(float)

#Establish new names to improve formatting
renameDict = {'Null Hypothesis Value = 1':'\makecell{Null \\\\ Value = 1}', 'Null Hypothesis Value = 11':'\makecell{Null \\\\ Value = 11}', 'Null Hypothesis Value = 1.6':'\makecell{Null \\\\ Value = 1.6}', 'Null Hypothesis Value = -4.8':'\makecell{Null \\\\ Value = -4.8}', 'Null Hypothesis Value = -2.5':'\makecell{Null \\\\ Value = -2.5}', 'Null Hypothesis Value = 9.7':'\makecell{Null \\\\ Value = 9.7}', 'Null Hypothesis Value = 0':'\makecell{Null \\\\ Value = 0}', 'Null Hypothesis Value = .3':'\makecell{Null \\\\ Value = 0.3}', 'Null Hypothesis Value = -6.1':'\makecell{Null \\\\ Value = -6.1}'}

vmtDF = vmtDF.rename(renameDict, axis='columns')
gasDF = gasDF.rename(renameDict, axis='columns')

#Format first-stage LOO table
didCols = ['', 'Null Hypothesis Value = 0']

pValuesDiD = (max(didLOO['P-Val(Eta=0)']), min(didLOO['P-Val(Eta=0)']))
pValuesDiDData = np.array(pValuesDiD).reshape((2,1))
pValuesDiDDF = p.DataFrame(np.hstack((vmtGasRows,pValuesDiDData)), columns=didCols)

pValuesDiDDF.loc[:, 'Null Hypothesis Value = 0'] = pValuesDiDDF.loc[:, 'Null Hypothesis Value = 0'].astype(float)

#Output tables
pValuesDiDDF.to_latex('{}LOO-DiDPValues.tex'.format(tableSaveDirec), float_format='%.2f', index=False, escape=False, na_rep='-')
vmtDF.to_latex('{}LOO-VMT.tex'.format(tableSaveDirec), float_format='%.2f', index=False, escape=False, na_rep='-')
gasDF.to_latex('{}LOO-Gas.tex'.format(tableSaveDirec), float_format='%.2f', index=False, escape=False, na_rep='-')
